{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://pptwinpics.oss-cn-beijing.aliyuncs.com/CDA%E8%AE%B2%E5%B8%88%E6%B0%B4%E5%8D%B0_20200314161940.png)\n",
    "\n",
    "大家好，我是 CDA 曹鑫。\n",
    "\n",
    "我的 Github 地址：https://github.com/imcda 。\n",
    "\n",
    "我的邮箱：caoxin@cda.cn 。\n",
    "\n",
    "这节课跟大家讲讲 Pandas。\n",
    "\n",
    "# Pandas 介绍\n",
    "要使用pandas，首先需要了解他主要两个数据结构：Series和DataFrame。\n",
    "\n",
    "# Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.0.1\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd \n",
    "print(pd.__version__)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     1.0\n",
      "1     3.0\n",
      "2     6.0\n",
      "3     NaN\n",
      "4    44.0\n",
      "5     1.0\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "s = pd.Series([1,3,6,np.nan,44,1])\n",
    "\n",
    "print(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Series的字符串表现形式为：索引在左边，值在右边。由于我们没有为数据指定索引。于是会自动创建一个0到N-1（N为长度）的整数型索引。\n",
    "\n",
    "# DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   a         b         c         d\n",
      "2016-01-01 -0.576394 -0.060499 -0.090954  0.134922\n",
      "2016-01-02 -0.275490 -0.476158  0.705120  0.221196\n",
      "2016-01-03  1.260930  0.330858 -0.571519  0.495860\n",
      "2016-01-04 -0.145246 -2.296362  0.449736 -1.544748\n",
      "2016-01-05 -1.150492  1.278814  0.655106 -1.526582\n",
      "2016-01-06  0.859137  0.218615 -1.161310 -1.638379\n"
     ]
    }
   ],
   "source": [
    "dates = pd.date_range('20160101',periods=6)\n",
    "df = pd.DataFrame(np.random.randn(6,4),index=dates,columns=['a','b','c','d'])\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`DataFrame`是一个表格型的数据结构，它包含有一组有序的列，每列可以是不同的值类型（数值，字符串，布尔值等）。`DataFrame`既有行索引也有列索引， 它可以被看做由`Series`组成的大字典。\n",
    "\n",
    "我们可以根据每一个不同的索引来挑选数据, 比如挑选 `b` 的元素:\n",
    "\n",
    "## DataFrame 的一些简单运用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2016-01-01   -0.060499\n",
      "2016-01-02   -0.476158\n",
      "2016-01-03    0.330858\n",
      "2016-01-04   -2.296362\n",
      "2016-01-05    1.278814\n",
      "2016-01-06    0.218615\n",
      "Freq: D, Name: b, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(df['b'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们在创建一组没有给定行标签和列标签的数据 df1:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   0  1   2   3\n",
      "0  0  1   2   3\n",
      "1  4  5   6   7\n",
      "2  8  9  10  11\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame(np.arange(12).reshape((3,4)))\n",
    "print(df1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "这样,他就会采取默认的从0开始 index. \n",
    "\n",
    "还有一种生成 df 的方法, 如下 df2:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A          B    C  D      E    F\n",
      "0  1.0 2013-01-02  1.0  3   test  foo\n",
      "1  1.0 2013-01-02  1.0  3  train  foo\n",
      "2  1.0 2013-01-02  1.0  3   test  foo\n",
      "3  1.0 2013-01-02  1.0  3  train  foo\n"
     ]
    }
   ],
   "source": [
    "df2 = pd.DataFrame({'A' : 1.,\n",
    "                    'B' : pd.Timestamp('20130102'),\n",
    "                    'C' : pd.Series(1,index=list(range(4)),dtype='float32'),\n",
    "                    'D' : np.array([3] * 4,dtype='int32'),\n",
    "                    'E' : pd.Categorical([\"test\",\"train\",\"test\",\"train\"]),\n",
    "                    'F' : 'foo'})\n",
    "                    \n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "这种方法能对每一列的数据进行特殊对待。\n",
    "\n",
    "如果想要查看数据中的类型, 我们可以用 dtype 这个属性:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A           float64\n",
      "B    datetime64[ns]\n",
      "C           float32\n",
      "D             int32\n",
      "E          category\n",
      "F            object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df2.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果想看对列的序号:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Int64Index([0, 1, 2, 3], dtype='int64')\n"
     ]
    }
   ],
   "source": [
    "print(df2.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "同样, 每种数据的名称也能看到:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['A', 'B', 'C', 'D', 'E', 'F'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(df2.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果只想看所有df2的值:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[1.0 Timestamp('2013-01-02 00:00:00') 1.0 3 'test' 'foo']\n",
      " [1.0 Timestamp('2013-01-02 00:00:00') 1.0 3 'train' 'foo']\n",
      " [1.0 Timestamp('2013-01-02 00:00:00') 1.0 3 'test' 'foo']\n",
      " [1.0 Timestamp('2013-01-02 00:00:00') 1.0 3 'train' 'foo']]\n"
     ]
    }
   ],
   "source": [
    "print(df2.values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "想知道数据的总结, 可以用 describe():"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         A    C    D\n",
       "count  4.0  4.0  4.0\n",
       "mean   1.0  1.0  3.0\n",
       "std    0.0  0.0  0.0\n",
       "min    1.0  1.0  3.0\n",
       "25%    1.0  1.0  3.0\n",
       "50%    1.0  1.0  3.0\n",
       "75%    1.0  1.0  3.0\n",
       "max    1.0  1.0  3.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果想翻转数据, transpose:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                     0                    1                    2  \\\n",
      "A                    1                    1                    1   \n",
      "B  2013-01-02 00:00:00  2013-01-02 00:00:00  2013-01-02 00:00:00   \n",
      "C                    1                    1                    1   \n",
      "D                    3                    3                    3   \n",
      "E                 test                train                 test   \n",
      "F                  foo                  foo                  foo   \n",
      "\n",
      "                     3  \n",
      "A                    1  \n",
      "B  2013-01-02 00:00:00  \n",
      "C                    1  \n",
      "D                    3  \n",
      "E                train  \n",
      "F                  foo  \n"
     ]
    }
   ],
   "source": [
    "print(df2.T)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果想对数据的 index 进行排序并输出:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A          B    C  D      E    F\n",
      "0  1.0 2013-01-02  1.0  3   test  foo\n",
      "1  1.0 2013-01-02  1.0  3  train  foo\n",
      "2  1.0 2013-01-02  1.0  3   test  foo\n",
      "3  1.0 2013-01-02  1.0  3  train  foo\n"
     ]
    }
   ],
   "source": [
    "print(df2.sort_index(axis=1, ascending=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果是对数据 值 排序输出:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A          B    C  D      E    F\n",
      "0  1.0 2013-01-02  1.0  3   test  foo\n",
      "1  1.0 2013-01-02  1.0  3  train  foo\n",
      "2  1.0 2013-01-02  1.0  3   test  foo\n",
      "3  1.0 2013-01-02  1.0  3  train  foo\n"
     ]
    }
   ],
   "source": [
    "print(df2.sort_values(by='B'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas 选择数据\n",
    "我们建立了一个 6X4 的矩阵数据。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>20</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             A   B   C   D\n",
       "2013-01-01   0   1   2   3\n",
       "2013-01-02   4   5   6   7\n",
       "2013-01-03   8   9  10  11\n",
       "2013-01-04  12  13  14  15\n",
       "2013-01-05  16  17  18  19\n",
       "2013-01-06  20  21  22  23"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates = pd.date_range('20130101', periods=6)\n",
    "df = pd.DataFrame(np.arange(24).reshape((6,4)),index=dates, columns=['A','B','C','D'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 简单的筛选\n",
    "如果我们想选取DataFrame中的数据，下面描述了两种途径, 他们都能达到同一个目的："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2013-01-01     0\n",
      "2013-01-02     4\n",
      "2013-01-03     8\n",
      "2013-01-04    12\n",
      "2013-01-05    16\n",
      "2013-01-06    20\n",
      "Freq: D, Name: A, dtype: int64\n",
      "2013-01-01     0\n",
      "2013-01-02     4\n",
      "2013-01-03     8\n",
      "2013-01-04    12\n",
      "2013-01-05    16\n",
      "2013-01-06    20\n",
      "Freq: D, Name: A, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df['A'])\n",
    "print(df.A)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "让选择跨越多行或多列:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            A  B   C   D\n",
      "2013-01-01  0  1   2   3\n",
      "2013-01-02  4  5   6   7\n",
      "2013-01-03  8  9  10  11\n"
     ]
    }
   ],
   "source": [
    "print(df[0:3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             A   B   C   D\n",
      "2013-01-02   4   5   6   7\n",
      "2013-01-03   8   9  10  11\n",
      "2013-01-04  12  13  14  15\n"
     ]
    }
   ],
   "source": [
    "print(df['20130102':'20130104'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果df[3:3]将会是一个空对象。后者选择20130102到20130104标签之间的数据，并且包括这两个标签。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 根据标签 loc\n",
    "同样我们可以使用标签来选择数据 loc, 本例子主要通过标签名字选择某一行数据， 或者通过选择某行或者所有行（:代表所有行）然后选其中某一列或几列数据。:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A    4\n",
      "B    5\n",
      "C    6\n",
      "D    7\n",
      "Name: 2013-01-02 00:00:00, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df.loc['20130102'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             A   B\n",
      "2013-01-01   0   1\n",
      "2013-01-02   4   5\n",
      "2013-01-03   8   9\n",
      "2013-01-04  12  13\n",
      "2013-01-05  16  17\n",
      "2013-01-06  20  21\n"
     ]
    }
   ],
   "source": [
    "print(df.loc[:,['A','B']]) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A    4\n",
      "B    5\n",
      "Name: 2013-01-02 00:00:00, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df.loc['20130102',['A','B']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 根据序列 iloc\n",
    "另外我们可以采用位置进行选择 iloc, 在这里我们可以通过位置选择在不同情况下所需要的数据例如选某一个，连续选或者跨行选等操作。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13\n"
     ]
    }
   ],
   "source": [
    "print(df.iloc[3,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             B   C\n",
      "2013-01-04  13  14\n",
      "2013-01-05  17  18\n"
     ]
    }
   ],
   "source": [
    "print(df.iloc[3:5,1:3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             B   C\n",
      "2013-01-02   5   6\n",
      "2013-01-04  13  14\n",
      "2013-01-06  21  22\n"
     ]
    }
   ],
   "source": [
    "print(df.iloc[[1,3,5],1:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在这里我们可以通过位置选择在不同情况下所需要的数据, 例如选某一个，连续选或者跨行选等操作。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 通过判断的筛选\n",
    "最后我们可以采用判断指令 (Boolean indexing) 进行选择. 我们可以约束某项条件然后选择出当前所有数据."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             A   B   C   D\n",
      "2013-01-04  12  13  14  15\n",
      "2013-01-05  16  17  18  19\n",
      "2013-01-06  20  21  22  23\n"
     ]
    }
   ],
   "source": [
    "print(df[df.A>8])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas 设置值\n",
    "## 创建数据 \n",
    "我们可以根据自己的需求, 用 pandas 进行更改数据里面的值, 或者加上一些空的,或者有数值的列.\n",
    "\n",
    "首先建立了一个 6X4 的矩阵数据。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>20</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             A   B   C   D\n",
       "2013-01-01   0   1   2   3\n",
       "2013-01-02   4   5   6   7\n",
       "2013-01-03   8   9  10  11\n",
       "2013-01-04  12  13  14  15\n",
       "2013-01-05  16  17  18  19\n",
       "2013-01-06  20  21  22  23"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates = pd.date_range('20130101', periods=6)\n",
    "df = pd.DataFrame(np.arange(24).reshape((6,4)),index=dates, columns=['A','B','C','D'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 根据位置设置 loc 和 iloc \n",
    "我们可以利用索引或者标签确定需要修改值的位置。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>0</td>\n",
       "      <td>2222</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>1111</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>20</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             A     B     C   D\n",
       "2013-01-01   0  2222     2   3\n",
       "2013-01-02   4     5     6   7\n",
       "2013-01-03   8     9  1111  11\n",
       "2013-01-04  12    13    14  15\n",
       "2013-01-05  16    17    18  19\n",
       "2013-01-06  20    21    22  23"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[2,2] = 1111\n",
    "df.loc['20130101','B'] = 2222\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 根据条件设置\n",
    "如果现在的判断条件是这样, 我们想要更改B中的数, 而更改的位置是取决于 A 的. 对于A大于4的位置. 更改B在相应位置上的数为0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>0</td>\n",
       "      <td>2222</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1111</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             A     B     C   D\n",
       "2013-01-01   0  2222     2   3\n",
       "2013-01-02   4     5     6   7\n",
       "2013-01-03   8     0  1111  11\n",
       "2013-01-04  12     0    14  15\n",
       "2013-01-05  16     0    18  19\n",
       "2013-01-06  20     0    22  23"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.B[df.A>4] = 0\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 按行或列设置\n",
    "如果对整列做批处理, 加上一列 ‘F’, 并将 F 列全改为 NaN, 如下:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>0</td>\n",
       "      <td>2222</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1111</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>23</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             A     B     C   D   F\n",
       "2013-01-01   0  2222     2   3 NaN\n",
       "2013-01-02   4     5     6   7 NaN\n",
       "2013-01-03   8     0  1111  11 NaN\n",
       "2013-01-04  12     0    14  15 NaN\n",
       "2013-01-05  16     0    18  19 NaN\n",
       "2013-01-06  20     0    22  23 NaN"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['F'] = np.nan\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 添加数据\n",
    "用上面的方法也可以加上 Series 序列（但是长度必须对齐）。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>F</th>\n",
       "      <th>E</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>0</td>\n",
       "      <td>2222</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>1111</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             A     B     C   D   F  E\n",
       "2013-01-01   0  2222     2   3 NaN  1\n",
       "2013-01-02   4     5     6   7 NaN  2\n",
       "2013-01-03   8     0  1111  11 NaN  3\n",
       "2013-01-04  12     0    14  15 NaN  4\n",
       "2013-01-05  16     0    18  19 NaN  5\n",
       "2013-01-06  20     0    22  23 NaN  6"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['E'] = pd.Series([1,2,3,4,5,6], index=pd.date_range('20130101',periods=6)) \n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "这样我们大概学会了如何对DataFrame中在自己想要的地方赋值或者增加数据。 下次课会将pandas如何处理丢失数据的过程。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://pptwinpics.oss-cn-beijing.aliyuncs.com/CDA%E8%AE%B2%E5%B8%88%E6%B0%B4%E5%8D%B0_20200314161940.png)\n",
    "\n",
    "# Pandas 处理丢失数据\n",
    "## 创建含 NaN 的矩阵 \n",
    "有时候我们导入或处理数据, 会产生一些空的或者是 NaN 数据,如何删除或者是填补这些 NaN 数据就是我们今天所要提到的内容.\n",
    "\n",
    "建立了一个6X4的矩阵数据并且把两个位置置为空."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>8</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>12</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>16</td>\n",
       "      <td>17.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>20</td>\n",
       "      <td>21.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             A     B     C   D\n",
       "2013-01-01   0   NaN   2.0   3\n",
       "2013-01-02   4   5.0   NaN   7\n",
       "2013-01-03   8   9.0  10.0  11\n",
       "2013-01-04  12  13.0  14.0  15\n",
       "2013-01-05  16  17.0  18.0  19\n",
       "2013-01-06  20  21.0  22.0  23"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates = pd.date_range('20130101', periods=6)\n",
    "df = pd.DataFrame(np.arange(24).reshape((6,4)),index=dates, columns=['A','B','C','D'])\n",
    "df.iloc[0,1] = np.nan\n",
    "df.iloc[1,2] = np.nan\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## pd.dropna()\n",
    "如果想直接去掉有 NaN 的行或列, 可以使用 dropna"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>8</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>12</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>16</td>\n",
       "      <td>17.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>20</td>\n",
       "      <td>21.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             A     B     C   D\n",
       "2013-01-03   8   9.0  10.0  11\n",
       "2013-01-04  12  13.0  14.0  15\n",
       "2013-01-05  16  17.0  18.0  19\n",
       "2013-01-06  20  21.0  22.0  23"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dropna(\n",
    "    axis=0,     # 0: 对行进行操作; 1: 对列进行操作\n",
    "    how='any'   # 'any': 只要存在 NaN 就 drop 掉; 'all': 必须全部是 NaN 才 drop \n",
    "    ) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## pd.fillna()\n",
    "如果是将 NaN 的值用其他值代替, 比如代替成 0:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>8</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>12</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>16</td>\n",
       "      <td>17.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>20</td>\n",
       "      <td>21.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             A     B     C   D\n",
       "2013-01-01   0   0.0   2.0   3\n",
       "2013-01-02   4   5.0   0.0   7\n",
       "2013-01-03   8   9.0  10.0  11\n",
       "2013-01-04  12  13.0  14.0  15\n",
       "2013-01-05  16  17.0  18.0  19\n",
       "2013-01-06  20  21.0  22.0  23"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.fillna(value=0) # 注意，这里是生成一张新表了"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## pd.isnull()\n",
    "判断是否有缺失数据 NaN, 为 True 表示缺失数据:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-01-01</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-02</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-03</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-04</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-05</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-01-06</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                A      B      C      D\n",
       "2013-01-01  False   True  False  False\n",
       "2013-01-02  False  False   True  False\n",
       "2013-01-03  False  False  False  False\n",
       "2013-01-04  False  False  False  False\n",
       "2013-01-05  False  False  False  False\n",
       "2013-01-06  False  False  False  False"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "检测在数据中是否存在 NaN, 如果存在就返回 True:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.any(df.isnull()) == True  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas 合并 concat\n",
    "pandas处理多组数据的时候往往会要用到数据的合并处理,使用 concat是一种基本的合并方式.而且concat中有很多参数可以调整,合并成你想要的数据形式.\n",
    "\n",
    "## axis (合并方向) \n",
    "axis=0是预设值，因此未设定任何参数时，函数默认axis=0。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     a    b    c    d\n",
      "0  0.0  0.0  0.0  0.0\n",
      "1  0.0  0.0  0.0  0.0\n",
      "2  0.0  0.0  0.0  0.0\n",
      "     a    b    c    d\n",
      "0  1.0  1.0  1.0  1.0\n",
      "1  1.0  1.0  1.0  1.0\n",
      "2  1.0  1.0  1.0  1.0\n",
      "     a    b    c    d\n",
      "0  2.0  2.0  2.0  2.0\n",
      "1  2.0  2.0  2.0  2.0\n",
      "2  2.0  2.0  2.0  2.0\n",
      "     a    b    c    d\n",
      "0  0.0  0.0  0.0  0.0\n",
      "1  0.0  0.0  0.0  0.0\n",
      "2  0.0  0.0  0.0  0.0\n",
      "0  1.0  1.0  1.0  1.0\n",
      "1  1.0  1.0  1.0  1.0\n",
      "2  1.0  1.0  1.0  1.0\n",
      "0  2.0  2.0  2.0  2.0\n",
      "1  2.0  2.0  2.0  2.0\n",
      "2  2.0  2.0  2.0  2.0\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "#定义资料集\n",
    "df1 = pd.DataFrame(np.ones((3,4))*0, columns=['a','b','c','d'])\n",
    "df2 = pd.DataFrame(np.ones((3,4))*1, columns=['a','b','c','d'])\n",
    "df3 = pd.DataFrame(np.ones((3,4))*2, columns=['a','b','c','d'])\n",
    "print(df1)\n",
    "print(df2)\n",
    "print(df3)\n",
    "\n",
    "#concat纵向合并\n",
    "res = pd.concat([df1, df2, df3], axis=0)\n",
    "\n",
    "#打印结果\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "仔细观察会发现结果的index是0, 1, 2, 0, 1, 2, 0, 1, 2，若要将index重置，请看例子二。\n",
    "\n",
    "## ignore_index (重置 index) ¶"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     a    b    c    d\n",
      "0  0.0  0.0  0.0  0.0\n",
      "1  0.0  0.0  0.0  0.0\n",
      "2  0.0  0.0  0.0  0.0\n",
      "3  1.0  1.0  1.0  1.0\n",
      "4  1.0  1.0  1.0  1.0\n",
      "5  1.0  1.0  1.0  1.0\n",
      "6  2.0  2.0  2.0  2.0\n",
      "7  2.0  2.0  2.0  2.0\n",
      "8  2.0  2.0  2.0  2.0\n"
     ]
    }
   ],
   "source": [
    "#承上一个例子，并将index_ignore设定为True\n",
    "res = pd.concat([df1, df2, df3], axis=0, ignore_index=True)\n",
    "\n",
    "#打印结果\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "结果的index变0, 1, 2, 3, 4, 5, 6, 7, 8。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## join (合并方式) \n",
    "join='outer'为预设值，因此未设定任何参数时，函数默认join='outer'。此方式是依照column来做纵向合并，有相同的column上下合并在一起，其他独自的column个自成列，原本没有值的位置皆以NaN填充。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     a    b    c    d\n",
      "1  0.0  0.0  0.0  0.0\n",
      "2  0.0  0.0  0.0  0.0\n",
      "3  0.0  0.0  0.0  0.0\n",
      "     b    c    d    e\n",
      "2  1.0  1.0  1.0  1.0\n",
      "3  1.0  1.0  1.0  1.0\n",
      "4  1.0  1.0  1.0  1.0\n",
      "     a    b    c    d    e\n",
      "1  0.0  0.0  0.0  0.0  NaN\n",
      "2  0.0  0.0  0.0  0.0  NaN\n",
      "3  0.0  0.0  0.0  0.0  NaN\n",
      "2  NaN  1.0  1.0  1.0  1.0\n",
      "3  NaN  1.0  1.0  1.0  1.0\n",
      "4  NaN  1.0  1.0  1.0  1.0\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "#定义资料集\n",
    "df1 = pd.DataFrame(np.ones((3,4))*0, columns=['a','b','c','d'], index=[1,2,3])\n",
    "df2 = pd.DataFrame(np.ones((3,4))*1, columns=['b','c','d','e'], index=[2,3,4])\n",
    "print(df1)\n",
    "print(df2)\n",
    "\n",
    "#纵向\"外\"合并df1与df2\n",
    "res = pd.concat([df1, df2], axis=0, join='outer', sort=False)\n",
    "res = df1.append(df2)\n",
    "print(res)\n",
    "\n",
    "# result = df1.join(df2)\n",
    "# result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A   B\n",
      "K0  A0  B0\n",
      "K1  A1  B1\n",
      "K2  A2  B2\n",
      "     C   D\n",
      "K0  C0  D0\n",
      "K2  C2  D2\n",
      "K3  C3  D3\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>K0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A   B    C    D\n",
       "K0  A0  B0   C0   D0\n",
       "K1  A1  B1  NaN  NaN\n",
       "K2  A2  B2   C2   D2"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],\n",
    "                              'B': ['B0', 'B1', 'B2']},\n",
    "                               index=['K0', 'K1', 'K2'])\n",
    "right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],\n",
    "                               'D': ['D0', 'D2', 'D3']},\n",
    "                                index=['K0', 'K2', 'K3']) \n",
    "\n",
    "print(left)\n",
    "print(right)\n",
    "result = left.join(right)\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "原理同上个例子的说明，但只有相同的column合并在一起，其他的会被抛弃。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     b    c    d\n",
      "1  0.0  0.0  0.0\n",
      "2  0.0  0.0  0.0\n",
      "3  0.0  0.0  0.0\n",
      "2  1.0  1.0  1.0\n",
      "3  1.0  1.0  1.0\n",
      "4  1.0  1.0  1.0\n"
     ]
    }
   ],
   "source": [
    "#承上一个例子\n",
    "\n",
    "#纵向\"内\"合并df1与df2\n",
    "res = pd.concat([df1, df2], axis=0, join='inner')\n",
    "\n",
    "#打印结果\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## join_axes (依照 axes 合并)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     a    b    c    d\n",
      "1  0.0  0.0  0.0  0.0\n",
      "2  0.0  0.0  0.0  0.0\n",
      "3  0.0  0.0  0.0  0.0\n",
      "     b    c    d    e\n",
      "2  1.0  1.0  1.0  1.0\n",
      "3  1.0  1.0  1.0  1.0\n",
      "4  1.0  1.0  1.0  1.0\n",
      "     a    b    c    d    b    c    d    e\n",
      "1  0.0  0.0  0.0  0.0  NaN  NaN  NaN  NaN\n",
      "2  0.0  0.0  0.0  0.0  1.0  1.0  1.0  1.0\n",
      "3  0.0  0.0  0.0  0.0  1.0  1.0  1.0  1.0\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "#定义资料集\n",
    "df1 = pd.DataFrame(np.ones((3,4))*0, columns=['a','b','c','d'], index=[1,2,3])\n",
    "df2 = pd.DataFrame(np.ones((3,4))*1, columns=['b','c','d','e'], index=[2,3,4])\n",
    "print(df1)\n",
    "print(df2)\n",
    "\n",
    "#依照`df1.index`进行横向合并\n",
    "res = pd.concat([df1, df2], axis=1, join_axes=[df1.index])\n",
    "\n",
    "#打印结果\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     a    b    c    d    b    c    d    e\n",
      "1  0.0  0.0  0.0  0.0  NaN  NaN  NaN  NaN\n",
      "2  0.0  0.0  0.0  0.0  1.0  1.0  1.0  1.0\n",
      "3  0.0  0.0  0.0  0.0  1.0  1.0  1.0  1.0\n",
      "4  NaN  NaN  NaN  NaN  1.0  1.0  1.0  1.0\n"
     ]
    }
   ],
   "source": [
    "#移除join_axes，并打印结果\n",
    "res = pd.concat([df1, df2], axis=1)\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## append (添加数据) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     a    b    c    d\n",
      "0  0.0  0.0  0.0  0.0\n",
      "1  0.0  0.0  0.0  0.0\n",
      "2  0.0  0.0  0.0  0.0\n",
      "3  1.0  1.0  1.0  1.0\n",
      "4  1.0  1.0  1.0  1.0\n",
      "5  1.0  1.0  1.0  1.0\n",
      "     a    b    c    d\n",
      "0  0.0  0.0  0.0  0.0\n",
      "1  0.0  0.0  0.0  0.0\n",
      "2  0.0  0.0  0.0  0.0\n",
      "3  1.0  1.0  1.0  1.0\n",
      "4  1.0  1.0  1.0  1.0\n",
      "5  1.0  1.0  1.0  1.0\n",
      "6  1.0  1.0  1.0  1.0\n",
      "7  1.0  1.0  1.0  1.0\n",
      "8  1.0  1.0  1.0  1.0\n",
      "     a    b    c    d\n",
      "0  0.0  0.0  0.0  0.0\n",
      "1  0.0  0.0  0.0  0.0\n",
      "2  0.0  0.0  0.0  0.0\n",
      "3  1.0  2.0  3.0  4.0\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "#定义资料集\n",
    "df1 = pd.DataFrame(np.ones((3,4))*0, columns=['a','b','c','d'])\n",
    "df2 = pd.DataFrame(np.ones((3,4))*1, columns=['a','b','c','d'])\n",
    "df3 = pd.DataFrame(np.ones((3,4))*1, columns=['a','b','c','d'])\n",
    "s1 = pd.Series([1,2,3,4], index=['a','b','c','d'])\n",
    "\n",
    "#将df2合并到df1的下面，以及重置index，并打印出结果\n",
    "res = df1.append(df2, ignore_index=True)\n",
    "print(res)\n",
    "\n",
    "#合并多个df，将df2与df3合并至df1的下面，以及重置index，并打印出结果\n",
    "res = df1.append([df2, df3], ignore_index=True)\n",
    "print(res)\n",
    "\n",
    "#合并series，将s1合并至df1，以及重置index，并打印出结果\n",
    "res = df1.append(s1, ignore_index=True)\n",
    "print(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas 合并 merge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas 读取CSV\n",
    "pandas可以读取与存取的资料格式有很多种，像csv、excel、json、html与pickle等…， 详细请看[官方说明文件](https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       股票代码 股票名称        交易日期                                          新浪概念    收盘价       涨跌幅       成交量 MACD_金叉死叉\n",
      "0  sz000002  万科Ａ  14/12/2016  含H股；融资融券；保障房；前海概念；保险重仓；QFII重仓；深圳本地；基金重仓；股权激励  22.29 -0.033391  41281457       NaN\n",
      "1  sz000002  万科Ａ  13/12/2016  含H股；融资融券；保障房；前海概念；保险重仓；QFII重仓；深圳本地；基金重仓；股权激励  23.06 -0.008598  48450098       NaN\n",
      "2  sz000002  万科Ａ  12/12/2016  含H股；融资融券；保障房；前海概念；保险重仓；QFII重仓；深圳本地；基金重仓；股权激励  23.26 -0.062475  78901945       NaN\n",
      "3  sz000002  万科Ａ  09/12/2016  含H股；融资融券；保障房；前海概念；保险重仓；QFII重仓；深圳本地；基金重仓；股权激励  24.81  0.002829  48168045       NaN\n",
      "4  sz000002  万科Ａ  08/12/2016  含H股；融资融券；保障房；前海概念；保险重仓；QFII重仓；深圳本地；基金重仓；股权激励  24.74 -0.015911  38055084       NaN\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd #加载模块\n",
    "\n",
    "#读取csv\n",
    "data = pd.read_csv('./CSVFiles/sz000002.csv')\n",
    "\n",
    "#打印出data\n",
    "print(data.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                股票代码 股票名称    收盘价       涨跌幅       成交量  MACD_金叉死叉\n",
      "交易日期                                                           \n",
      "14/12/2016  sz000002  万科Ａ  22.29 -0.033391  41281457        NaN\n",
      "13/12/2016  sz000002  万科Ａ  23.06 -0.008598  48450098        NaN\n",
      "12/12/2016  sz000002  万科Ａ  23.26 -0.062475  78901945        NaN\n",
      "09/12/2016  sz000002  万科Ａ  24.81  0.002829  48168045        NaN\n",
      "08/12/2016  sz000002  万科Ａ  24.74 -0.015911  38055084        NaN\n",
      "07/12/2016  sz000002  万科Ａ  25.14 -0.009456  49462389        NaN\n",
      "06/12/2016  sz000002  万科Ａ  25.38 -0.004706  35645541        NaN\n",
      "05/12/2016  sz000002  万科Ａ  25.50 -0.035917  68906336        NaN\n",
      "02/12/2016  sz000002  万科Ａ  26.45 -0.019281  51572972        NaN\n",
      "01/12/2016  sz000002  万科Ａ  26.97 -0.000371  95017059        NaN\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    # 该参数为数据在电脑中的路径，可以不填写\n",
    "    filepath_or_buffer='./CSVFiles/sz000002.csv',\n",
    "    # 线上读取\n",
    "#     filepath_or_buffer='https://bit.ly/sz000002',\n",
    "\n",
    "    # 该参数代表数据的分隔符，csv文件默认是逗号。其他常见的是'\\t'\n",
    "    sep=',',\n",
    "    # 该参数代表跳过数据文件的的第1行不读入\n",
    "#     skiprows=1,\n",
    "    # nrows，只读取前n行数据，若不指定，读入全部的数据\n",
    "    nrows=10,\n",
    "    # 将指定列的数据识别为日期格式。若不指定，时间数据将会以字符串形式读入。一开始先不用。\n",
    "    # parse_dates=['交易日期'],\n",
    "    # 将指定列设置为index。若不指定，index默认为0, 1, 2, 3, 4...\n",
    "    index_col=['交易日期'],\n",
    "    # 读取指定的这几列数据，其他数据不读取。若不指定，读入全部列\n",
    "    usecols=['交易日期', '股票代码', '股票名称', '收盘价', '涨跌幅', '成交量', 'MACD_金叉死叉'],\n",
    "    # 当某行数据有问题时，报错。设定为False时即不报错，直接跳过该行。当数据比较脏乱的时候用这个。\n",
    "    error_bad_lines=False,\n",
    "    # 将数据中的null识别为空值\n",
    "    na_values='NULL',\n",
    "    # 更多其他参数，请直接搜索\"pandas read_csv\"，要去逐个查看一下。比较重要的，header等\n",
    ")\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(10, 6)\n"
     ]
    }
   ],
   "source": [
    "# =====看数据\n",
    "print(df.shape)  # 输出dataframe有多少行、多少列。\n",
    "# print(df.shape[0])  # 取行数量，相应的列数量就是df.shape[1]\n",
    "# print(df.columns)  # 顺序输出每一列的名字，演示如何for语句遍历。\n",
    "# print(df.index)  # 顺序输出每一行的名字，可以for语句遍历。\n",
    "# print(df.dtypes)  # 数据每一列的类型不一样，比如数字、字符串、日期等。该方法输出每一列变量类型\n",
    "# print(df.head(3))  # 看前3行的数据，默认是5。与自然语言很接近\n",
    "# print(df.tail(3))  # 看最后3行的数据，默认是5。\n",
    "# print(df.sample(n=3))  # 随机抽取3行，想要去固定比例的话，可以用frac参数\n",
    "# print(df.describe())  # 非常方便的函数，对每一类数据有直观感受；只会对数字类型的列有效"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                股票代码 股票名称    收盘价       涨跌幅       成交量  MACD_金叉死叉\n",
      "交易日期                                                           \n",
      "14/12/2016  sz000002  万科Ａ  22.29 -0.033391  41281457        NaN\n",
      "13/12/2016  sz000002  万科Ａ  23.06 -0.008598  48450098        NaN\n",
      "12/12/2016  sz000002  万科Ａ  23.26 -0.062475  78901945        NaN\n",
      "09/12/2016  sz000002  万科Ａ  24.81  0.002829  48168045        NaN\n",
      "08/12/2016  sz000002  万科Ａ  24.74 -0.015911  38055084        NaN\n",
      "07/12/2016  sz000002  万科Ａ  25.14 -0.009456  49462389        NaN\n",
      "06/12/2016  sz000002  万科Ａ  25.38 -0.004706  35645541        NaN\n",
      "05/12/2016  sz000002  万科Ａ  25.50 -0.035917  68906336        NaN\n",
      "02/12/2016  sz000002  万科Ａ  26.45 -0.019281  51572972        NaN\n",
      "01/12/2016  sz000002  万科Ａ  26.97 -0.000371  95017059        NaN\n"
     ]
    }
   ],
   "source": [
    "# 对print出的数据格式进行修正\n",
    "pd.set_option('expand_frame_repr', False)  # 当列太多时不换行\n",
    "pd.set_option('max_colwidth', 100) # 设定每一列的最大宽度，恢复原设置的方法，pd.reset_option('max_colwidth')\n",
    "# 更多设置请见http://pandas.pydata.org/pandas-docs/stable/options.html\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "交易日期\n",
      "14/12/2016    9.201637e+08\n",
      "13/12/2016    1.117259e+09\n",
      "12/12/2016    1.835259e+09\n",
      "09/12/2016    1.195049e+09\n",
      "08/12/2016    9.414828e+08\n",
      "07/12/2016    1.243484e+09\n",
      "06/12/2016    9.046838e+08\n",
      "05/12/2016    1.757112e+09\n",
      "02/12/2016    1.364105e+09\n",
      "01/12/2016    2.562610e+09\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# =====列操作\n",
    "# 行列加减乘除\n",
    "# print(df['股票名称'] + '_地产')  # 字符串列可以直接加上字符串，对整列进行操作\n",
    "# print(df['收盘价'] * 100)  # 数字列直接加上或者乘以数字，对整列进行操作。\n",
    "print(df['收盘价'] * df['成交量'])  # 两列之间可以直接操作。收盘价*成交量计算出的是什么？\n",
    "# 新增一列\n",
    "# df['股票名称+行业'] = df['股票名称'] + '_地产'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# =====统计函数\n",
    "# print(df['收盘价'].mean())  # 求一整列的均值，返回一个数。会自动排除空值。\n",
    "# print(df[['收盘价', '成交量']].mean())  # 求两列的均值，返回两个数，Series\n",
    "# print(df[['收盘价', '成交量']])\n",
    "# print(df[['收盘价', '成交量']].mean(axis=1))  # 求两列的均值，返回DataFrame。axis=0或者1要搞清楚。\n",
    "# axis=1，代表对整几列进行操作。axis=0（默认）代表对几行进行操作。实际中弄混很正常，到时候试一下就知道了。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(df['收盘价'].max())  # 最大值\n",
    "# print(df['收盘价'].min())  # 最小值\n",
    "# print(df['收盘价'].std())  # 标准差\n",
    "# print(df['收盘价'].count()) # 非空的数据的数量\n",
    "# print(df['收盘价'].median())  # 中位数\n",
    "# print(df['收盘价'].quantile(0.5))  # 50%分位数\n",
    "# 肯定还有其他的函数计算其他的指标，在实际使用中遇到可以自己搜索"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# =====筛选操作，根据指定的条件，筛选出相关拿数据。\n",
    "# print(df['股票代码'] == 'sz000002')  # 判断股票代码是否等于sz000002\n",
    "# print(df[df['股票代码'] == 'sz000002'])  # 将判断为True的输出：选取股票代码等于sz000002的行\n",
    "# print(df[df['股票代码'].isin(['sz000002', 'sz000003 ', 'sz000004'])])  # 选取股票代码等于sz000002的行\n",
    "# print(df[df['收盘价'] >= 24.0])  # 选取收盘价大于24的行\n",
    "# print(df[(df.index >= '03/12/2016') & (df.index <= '06/12/2016')])  # 两个条件，或者的话就是|\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# =====排序函数\n",
    "# df.reset_index(inplace=True)\n",
    "# print(df.sort_values(by=['股票名称'], ascending=[1, 1]))  # by参数指定按照什么进行排序，acsending参数指定是顺序还是逆序，1顺序，0逆序\n",
    "# print(df.sort_values(by=['股票名称', '交易日期], ascending=[1, 1]))  # 按照多列进行排序\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                股票代码    收盘价       涨跌幅\n",
      "交易日期                                 \n",
      "14/12/2016  sz000002  22.29 -0.033391\n",
      "13/12/2016  sz000002  23.06 -0.008598\n",
      "12/12/2016  sz000002  23.26 -0.062475\n",
      "09/12/2016  sz000002  24.81  0.002829\n",
      "08/12/2016  sz000002  24.74 -0.015911\n",
      "07/12/2016  sz000002  25.14 -0.009456\n",
      "06/12/2016  sz000002  25.38 -0.004706\n",
      "05/12/2016  sz000002  25.50 -0.035917\n",
      "02/12/2016  sz000002  26.45 -0.019281\n",
      "01/12/2016  sz000002  26.97 -0.000371\n",
      "           股票名称    收盘价       涨跌幅\n",
      "交易日期                            \n",
      "07/12/2016  万科Ａ  25.14 -0.009456\n",
      "06/12/2016  万科Ａ  25.38 -0.004706\n",
      "05/12/2016  万科Ａ  25.50 -0.035917\n",
      "02/12/2016  万科Ａ  26.45 -0.019281\n",
      "01/12/2016  万科Ａ  26.97 -0.000371\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>股票代码</th>\n",
       "      <th>收盘价</th>\n",
       "      <th>涨跌幅</th>\n",
       "      <th>股票名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sz000002</td>\n",
       "      <td>22.29</td>\n",
       "      <td>-0.033391</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sz000002</td>\n",
       "      <td>23.06</td>\n",
       "      <td>-0.008598</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sz000002</td>\n",
       "      <td>23.26</td>\n",
       "      <td>-0.062475</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sz000002</td>\n",
       "      <td>24.81</td>\n",
       "      <td>0.002829</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sz000002</td>\n",
       "      <td>24.74</td>\n",
       "      <td>-0.015911</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>sz000002</td>\n",
       "      <td>25.14</td>\n",
       "      <td>-0.009456</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>sz000002</td>\n",
       "      <td>25.38</td>\n",
       "      <td>-0.004706</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>sz000002</td>\n",
       "      <td>25.50</td>\n",
       "      <td>-0.035917</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>sz000002</td>\n",
       "      <td>26.45</td>\n",
       "      <td>-0.019281</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>sz000002</td>\n",
       "      <td>26.97</td>\n",
       "      <td>-0.000371</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NaN</td>\n",
       "      <td>25.14</td>\n",
       "      <td>-0.009456</td>\n",
       "      <td>万科Ａ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>25.38</td>\n",
       "      <td>-0.004706</td>\n",
       "      <td>万科Ａ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>25.50</td>\n",
       "      <td>-0.035917</td>\n",
       "      <td>万科Ａ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>26.45</td>\n",
       "      <td>-0.019281</td>\n",
       "      <td>万科Ａ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>26.97</td>\n",
       "      <td>-0.000371</td>\n",
       "      <td>万科Ａ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        股票代码    收盘价       涨跌幅 股票名称\n",
       "0   sz000002  22.29 -0.033391  NaN\n",
       "1   sz000002  23.06 -0.008598  NaN\n",
       "2   sz000002  23.26 -0.062475  NaN\n",
       "3   sz000002  24.81  0.002829  NaN\n",
       "4   sz000002  24.74 -0.015911  NaN\n",
       "5   sz000002  25.14 -0.009456  NaN\n",
       "6   sz000002  25.38 -0.004706  NaN\n",
       "7   sz000002  25.50 -0.035917  NaN\n",
       "8   sz000002  26.45 -0.019281  NaN\n",
       "9   sz000002  26.97 -0.000371  NaN\n",
       "10       NaN  25.14 -0.009456  万科Ａ\n",
       "11       NaN  25.38 -0.004706  万科Ａ\n",
       "12       NaN  25.50 -0.035917  万科Ａ\n",
       "13       NaN  26.45 -0.019281  万科Ａ\n",
       "14       NaN  26.97 -0.000371  万科Ａ"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# =====两个df上下合并操作，append操作\n",
    "df1 = df.iloc[0:10][['股票代码', '收盘价', '涨跌幅']]\n",
    "df2 = df.iloc[5:15][['股票名称', '收盘价', '涨跌幅']]\n",
    "print(df1)\n",
    "print(df2)\n",
    "\n",
    "# print(df1.append(df2))  # append操作，将df1和df2上下拼接起来。注意观察拼接之后的index\n",
    "df3 = df1.append(df2, ignore_index=True)  # ignore_index参数，用户重新确定index\n",
    "df3\n",
    "# 当两个df的列名不完全相同的时候，来自两个df的所有列都会保留"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>股票代码</th>\n",
       "      <th>收盘价</th>\n",
       "      <th>涨跌幅</th>\n",
       "      <th>股票名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>sz000002</td>\n",
       "      <td>26.97</td>\n",
       "      <td>-0.000371</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>26.97</td>\n",
       "      <td>-0.000371</td>\n",
       "      <td>万科Ａ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        股票代码    收盘价       涨跌幅 股票名称\n",
       "9   sz000002  26.97 -0.000371  NaN\n",
       "14       NaN  26.97 -0.000371  万科Ａ"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# =====对数据进行去重\n",
    "# df3中有重复的行数，我们如何将重复的行数去除？\n",
    "df3.drop_duplicates(\n",
    "    subset=['股票代码'],  # subset参数用来指定根据哪类类数据来判断是否重复。若不指定，则用全部列的数据来判断是否重复\n",
    "    keep='last',  # 在去除重复值的时候，我们是保留上面一行还是下面一行？first保留上面一行，last保留下面一行，False就是一行都不保留\n",
    "    inplace=True\n",
    ")\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# =====其他常用重要函数\n",
    "# print(df.rename(columns={'MACD_金叉死叉': '金叉死叉', '涨跌幅': '涨幅'}))  # rename函数给变量修改名字。使用dict将要修改的名字传给columns参数\n",
    "# print(df.empty)  # 判断一个df是不是为空，此处输出不为空\n",
    "# print(pd.DataFrame().empty)  # pd.DataFrame()创建一个空的DataFrame，此处输出为空\n",
    "# print(df.T)  # 将数据转置，行变成列，很有用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# =====输出\n",
    "# print df\n",
    "# df.to_csv('output.csv')\n",
    "# df.to_csv('output.csv', index=False)\n",
    "# df.to_csv('output_gbk.csv', index=False, encoding='gbk')  # 指定编码格式"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](https://pptwinpics.oss-cn-beijing.aliyuncs.com/CDA%E8%AE%B2%E5%B8%88%E6%B0%B4%E5%8D%B0_20200314161940.png)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "216px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
